package dBConnection;

import java.sql.*;
import java.util.ArrayList;

import universityMap.Agreement;
import universityMap.Institution;

/**
 * This class establish a connection with the GRIA's database.
 * @author Andrea Piermarteri, Matteo Micheletti, Diego Cervellini.
 *
 */
public class dBConnector {
	public Connection conn;

	/**
	 * Constructor of dBConnector.
	 * @param url
	 * @param dbName
	 * @param user
	 * @param pwd
	 * @throws ClassNotFoundException
	 */
	public dBConnector(String url, String dbName, String user, String pwd)
			throws ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		try {
			this.conn = DriverManager.getConnection(url + dbName, user, pwd);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * This method retrieves the nth Institution from the db's table.
	 * @param n
	 * @return the nth Institution.
	 * @throws SQLException
	 */
	public Institution getInstitution(int n) throws SQLException {
		Statement stmt = this.conn.createStatement();
		ResultSet rs;
		if (stmt.execute("SELECT * FROM Institutions WHERE id = " + n + ";")) {
			rs = stmt.getResultSet();
		} else
			return null;
		rs.first();
		Institution r = new Institution(rs.getInt(1), rs.getString(2), rs
				.getString(3), rs.getString(4), rs.getDouble(5), rs
				.getDouble(6), rs.getDouble(7), rs.getString(8), rs
				.getString(9), rs.getString(10));
		rs.close();
		return r;
	}

	/**
	 * Retrieves all the Institutions from the db's table.
	 * @return the ArrayList of all the Institutions.
	 * @throws SQLException
	 */
	public ArrayList<Institution> getInstitutions() throws SQLException {
		Statement stmt = this.conn.createStatement();
		ResultSet rs;
		if (stmt.execute("SELECT * FROM Institutions;")) {
			rs = stmt.getResultSet();
		} else
			rs = null;
		ArrayList<Institution> array = new ArrayList<Institution>();
		rs.beforeFirst();
		while (true) {
			if (rs.next()) {
				array.add(new Institution(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5), rs
						.getDouble(6), rs.getDouble(7), rs.getString(8), rs
						.getString(9), rs.getString(10)));
			} else
				break;
		}
		rs.close();
		return array;
	}

	/**
	 * Retrieves all the Agreements from the db's table.
	 * @return the ArrayList of all the Agreements.
	 * @throws SQLException
	 */
	public ArrayList<Agreement> getAgreements() throws SQLException {
		ArrayList<Institution> insts = this.getInstitutions();
		Institution i1 = null;
		Institution i2 = null;
		int idi1, idi2;
		Statement stmt = this.conn.createStatement();
		ResultSet rs;
		if (stmt.execute("SELECT * FROM Agreements;")) {
			rs = stmt.getResultSet();
		} else
			return null;
		ArrayList<Agreement> array = new ArrayList<Agreement>();
		rs.beforeFirst();
		while (true) {
			if (rs.next()) {
				idi1 = rs.getInt(2);
				idi2 = rs.getInt(3);
				for (Institution inst : insts) {
					if (inst.getId() == idi1) {
						i1 = inst;
						break;
					}
				}
				for (Institution inst : insts) {
					if (inst.getId() == idi2) {
						i2 = inst;
						break;
					}
				}
				array.add(new Agreement(rs.getInt(1), i1, i2, rs.getString(4),
						rs.getString(5), rs.getInt(6), rs.getInt(7), rs
								.getInt(8), rs.getInt(9), rs.getString(10), rs
								.getString(11)));
			} else
				break;
		}
		rs.close();
		return array;
	}

	/**
	 * This method closes the database connection.
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException {
		this.conn.close();
	}

}
